Skip to main content

第15章 MySQL优化（一）

第15章 MySQL优化（一）​

一、优化​

  • 表结构是否合理，范式化？数据类型？varchar（可变长，便于压缩）和char还有text（存储一个指针，执行外部存储的原文）读取记录的时候，考虑到page的大小有限，如果一条记录的大小就占用了好几个page显然是不合理的，所以特别长的文本需要使用text类型，这样读取出来的text部分就是一个指针，性能更好。
  • 什么样的索引科学？什么样的存储引擎好？锁、隔离机制？所有的内存用到缓存合适吗？
  • 当硬件赶不上业务的时候，该怎么调整服务器性能？
  • 能选数字就选数字，数字类型少很多字节，而不是选择字符类型，这样性能会更好

二、索引的建立优缺点​

  • 提高select性能最好的办法就是在经常查询的列上面建立索引
  • 索引条目充当指向表行的指针，允许查询快速确定哪些行符合WHERE子句中的条件，并检索这些行的其他列值。
  • 索引尽可能建立在取值为不为空，而且值唯一（或者很少有重复的）列上面
  • 并不是索引建立的越多越好，不必要的索引建立会浪费空间和时间来决定用什么索引搜索
  • 如果建立了一堆索引，对于数据更新的时候，索引改变需要调整，那么由于大量的索引建立，导致调整b+ tree需要消耗大量的时间（所以需要管理者权衡考虑！）
  • 系统会自动在自增的主键建立索引，这样最大的好处就是在join操作的时候，可以快速定位到目标
  • 当表格比较小的时候，建立索引很浪费，不如全表扫描
  • 获取要findall的时候，索引也很浪费，本来就是要把整个表数据读取出来，索引失去意义

二、聚簇索引​

  • 聚簇索引：索引的顺序和数据存储的顺序完全一致的索引叫做聚簇索引。InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，聚簇索引一般就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。

  • 为什么快：索引记录的位置和存储的位置在顺序上是一样的，这样找起来就非常快。例如：某个节点有多个子节点，从第一个节点到最后一个节点排出来顺序是依次递增的时候，这样显然在查找一个范围的时候就非常快，可以按照顺序来读取磁盘。反之如果存储的地址顺序是乱的，在查找一个范围的时候就不能一次读取一片区域出来，效率就会变低，要读取多次。

三、复合索引​

  • 根据多个列建立索引，会有不同列的优先级，比如姓名相同按照年龄排序，年龄相同安装学号排序
  • 最多16列，有数量限制
  • 建立三个单独的索引比建立一个复合的索引要浪费空间，B+树的叶子节点存储要索引的值还有一个指向硬盘的位置，而建立三个单独的索引，就需要三个树，叶子结点存储的同理，也就是说建立复合索引相对来说更好。而且调整一棵树的速度比调整三棵树的效率显然要快的。
  • 所以复合索引的顺序非常的重要！

四、前缀索引​

  • 假如某些国家的人名非常长，达到了300个字符，在建立索引的时候可以只使用前N个字符
  • 这样可以减少IO操作，单条记录的大小，然后增加每个page能够读取到记录的条目数量

五、Hash索引​

  • 通过Hash算法（常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞（两个不同关键字的Hash值相同）可以通过reHash一下，全部重新打乱，增加Hash值的长度，或者通过链表的方式往后面增加
  • hash索引时候单值的查找

六、Row format​

  • REDUNDANT：有冗余，假如varchar(100)，实际写入20个字符，实际存储的时候占用100个字符（前缀索引长度可以达到767）
  • COMPACT：紧凑的，假如varchar(100)，实际写入20个字符，实际存储的时候占用20个字符（前缀索引长度可以达到767）
  • DYNAMIC：动态的，假如varchar(100)，实际写入20个字符，20个字符都是完全一样的，实际存储的时候不压缩（前缀索引长度可以达到3072）
  • COMPRESSED：压缩的，假如varchar(100)，实际写入20个字符，20个字符都是完全一样的，实际存储的时候会压缩一下（前缀索引长度可以达到3072）
  • 有些时候不压缩反而比较好，因为每个record都是长度一样的时候，反而可能比较利于查找。所以一般来说要有所权衡。

七、全文索引​

  • 参考elasticsearch那一章节

八、不常用的数据处理​

  • 如果一个表格里面的，很多查询对于这个表都不包含一些列，怎么办？
  • 考虑切开这个表，然后使用join连接，拆两个表，常用的放一个，不常用的放一个
  • 数据库的block按照列存储，好处就是统计一列的总和，可能效率非常高，缺点是多次插入一个行的时候非常不适合。

九、blob优化​

  • 是一个二进制数组
  • 不存储在表格里面，通过指针关联
  • 如果要判断两个blob相等，没有必要把两个特别大的blob读取出来，可以通过计算hash值，如果hash不等数值，那肯定不相等

十、table_open_cache​

  • 数值越大，可以同时打开的表格的数量就越多，这个cache存储的是打开文件的文件标识符
  • 假如有三个数据库连接，三个连接同时都在操作tableA，那么缓存中就会有三个这样的表，而不是一个表
  • 如果有200个并发的连接，table_open_cache的值应该设置为：200 * N,N就是执行查询的时候join表的最大数量
  • 这个值并不是越大越好，服务器内存有限，此外到底能同时打开多少个表，还受到操作系统的限制（文件标识符受到限制）
  • 缓存满了怎么办？最近最少使用的时候，就会把这个文件描述符关掉，flush之后再加载其他表进来，也会触发落硬盘的操作！

问题：查看mysql的时候发现系统打开的表格数量好几百为什么呢？

  • 一个是因为系统表，有些执行操作的时候，需要用到系统表，所以打开了。
  • 假如有三个数据库连接，三个连接同时都在操作tableA，那么缓存中就会有三个这样的表，而不是一个表，这样也会增加一些open table数量
  • 一些sql语句执行的时候，系统在优化之后执行可能会创建一些view或者临时表，因此就会发现打开的表数量很大

十一、innodb_page_size​

  • 数据库默认按照行存储的，如果存满了一个1page就把这一页load到内存里面
  • 行的尺寸根页的大小相关，每行的数据的大小应该是innodb_page_size的一半而且必须要小一点，因为有一些元数据在里面，需要占用空间
  • 这就是mysql存储行的最大上限